package com.cq.xjms.service.impl;

import java.util.List;

import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.extremecomponents.table.limit.Limit;

import com.cq.xjms.model.Score;
import com.cq.xjms.model.Student;
import com.cq.xjms.service.ScoreManager;
import com.cq.xjms.service.StudentManager;

public class ScoreManagerImpl extends BaseManagerImpl<Score> implements ScoreManager{
	private static Logger LOG = Logger.getLogger(ScoreManagerImpl.class);
	
	public List<Score> queryScore(String studentName,String courseName,int courseStatus,Limit limit) {
		StringBuffer hql = new StringBuffer();
		hql.append("select c from Score c where c.point>0");
		if(StringUtils.isNotEmpty(studentName)){
			hql.append(" and c.student.name=:studentName");
		}
		if(StringUtils.isNotEmpty(courseName)){
			hql.append(" and c.course.name=:courseName");
		}
		if(courseStatus!=0){
			hql.append(" and c.course.status=:courseStatus");
		}
		
		hql.append(" order by c.createDate desc");
		List<Score> scores = null;
		try{
			Query query = this.query(hql.toString());
			if(StringUtils.isNotEmpty(studentName)){
				query.setParameter("studentName", studentName);
			}
			if(StringUtils.isNotEmpty(courseName)){
				query.setParameter("courseName", courseName);
			}
			if(courseStatus!=0){
				query.setParameter("courseStatus", courseStatus);
			}
			
			if (limit != null) {
				query.setFirstResult((limit.getPage() - 1)
						* limit.getCurrentRowsDisplayed());
				query.setMaxResults(limit.getCurrentRowsDisplayed());
			}
			scores =  query.getResultList();
		}catch(Exception e){
			LOG.error("query student failed!", e);
		}
		return scores;
	}
	
	public List<Score> queryScoreByStudent(Long studentId,String yearterm,Limit limit) {
		StringBuffer hql = new StringBuffer();
		hql.append("select c from Score c left join fetch c.course left join fetch c.course.teacher where c.point>0 ");
		if(studentId !=0){
			hql.append(" and c.student.id=:studentId");
		}
		if(StringUtils.isNotEmpty(yearterm)){
			hql.append(" and c.course.term like:yearterm");
		}
		
		hql.append(" order by c.createDate desc");
		List<Score> scores = null;
		try{
			Query query = this.query(hql.toString());
			if(studentId !=0){
				query.setParameter("studentId", studentId);
			}
			if(StringUtils.isNotEmpty(yearterm)){
				query.setParameter("yearterm", "%"+yearterm+"%");
			}
			
			
			if (limit != null) {
				query.setFirstResult((limit.getPage() - 1)
						* limit.getCurrentRowsDisplayed());
				query.setMaxResults(limit.getCurrentRowsDisplayed());
			}
			scores =  query.getResultList();
		}catch(Exception e){
			LOG.error("query student failed!", e);
		}
		return scores;
	}
	
	
	public List<Score> queryCourseByStudent(Long studentId,String yearterm,Limit limit) {
		StringBuffer hql = new StringBuffer();
		hql.append("select c from Score c left join fetch c.course left join fetch c.course.teacher where 1=1 ");
		if(studentId !=0){
			hql.append(" and c.student.id=:studentId");
		}
		if(StringUtils.isNotEmpty(yearterm)){
			hql.append(" and c.course.term like:yearterm");
		}
		
		hql.append(" order by c.createDate desc");
		List<Score> scores = null;
		try{
			Query query = this.query(hql.toString());
			if(studentId !=0){
				query.setParameter("studentId", studentId);
			}
			if(StringUtils.isNotEmpty(yearterm)){
				query.setParameter("yearterm", "%"+yearterm+"%");
			}
			
			
			if (limit != null) {
				query.setFirstResult((limit.getPage() - 1)
						* limit.getCurrentRowsDisplayed());
				query.setMaxResults(limit.getCurrentRowsDisplayed());
			}
			scores =  query.getResultList();
		}catch(Exception e){
			LOG.error("query student failed!", e);
		}
		return scores;
	}
	
	
	public int countScore(String studentName,String courseName,int courseStatus) {
		StringBuffer hql = new StringBuffer();
		hql.append("select count(c) from Score c  where c.point>0 ");
		if(StringUtils.isNotEmpty(studentName)){
			hql.append(" and c.student.name=:studentName");
		}
		if(StringUtils.isNotEmpty(courseName)){
			hql.append(" and c.course.name=:courseName");
		}
		if(courseStatus!=0){
			hql.append(" and c.course.status=:courseStatus");
		}
		
		try{
			Query query = this.query(hql.toString());
			if(StringUtils.isNotEmpty(studentName)){
				query.setParameter("studentName", studentName);
			}
			if(StringUtils.isNotEmpty(courseName)){
				query.setParameter("courseName", courseName);
			}
			if(courseStatus!=0){
				query.setParameter("courseStatus", courseStatus);
			}
			
			
			Object result =  query.getSingleResult();
			return result == null ? 0
					: result instanceof Integer ? ((Integer) result).intValue()
							: ((Long) result).intValue();
		}catch(Exception e){
			LOG.error("count student failed!", e);
		}
		return 0;
	}
	public int countScoreByStudent(Long studentId,String yearterm) {
		StringBuffer hql = new StringBuffer();
		hql.append("select count(c) from Score c  where c.point >0 ");
		if(studentId !=0){
			hql.append(" and c.student.id=:studentId");
		}
		if(StringUtils.isNotEmpty(yearterm)){
			hql.append(" and c.course.term like:yearterm");
		}
		
		try{
			Query query = this.query(hql.toString());
			if(studentId !=0){
				query.setParameter("studentId", studentId);
			}
			if(StringUtils.isNotEmpty(yearterm)){
				query.setParameter("yearterm", "%"+yearterm+"%");
			}
			
			
			Object result =  query.getSingleResult();
			return result == null ? 0
					: result instanceof Integer ? ((Integer) result).intValue()
							: ((Long) result).intValue();
		}catch(Exception e){
			LOG.error("count student failed!", e);
		}
		return 0;
	}
	
	public int countCourseByStudent(Long studentId,String yearterm) {
		StringBuffer hql = new StringBuffer();
		hql.append("select count(c) from Score c  where 1=1 ");
		if(studentId !=0){
			hql.append(" and c.student.id=:studentId");
		}
		if(StringUtils.isNotEmpty(yearterm)){
			hql.append(" and c.course.term like:yearterm");
		}
		
		try{
			Query query = this.query(hql.toString());
			if(studentId !=0){
				query.setParameter("studentId", studentId);
			}
			if(StringUtils.isNotEmpty(yearterm)){
				query.setParameter("yearterm", "%"+yearterm+"%");
			}
			
			
			Object result =  query.getSingleResult();
			return result == null ? 0
					: result instanceof Integer ? ((Integer) result).intValue()
							: ((Long) result).intValue();
		}catch(Exception e){
			LOG.error("count student failed!", e);
		}
		return 0;
	}

	@Override
	public int countCourseNoPassByStudent(Long studentId) {
		// TODO Auto-generated method stub
		StringBuffer hql = new StringBuffer();
		hql.append("select count(c) from Score c  where c.point>0 and c.point<60 ");
		if(studentId !=0){
			hql.append(" and c.student.id=:studentId");
		}
	
		try{
			Query query = this.query(hql.toString());
			if(studentId !=0){
				query.setParameter("studentId", studentId);
			}
			
			
			Object result =  query.getSingleResult();
			return result == null ? 0
					: result instanceof Integer ? ((Integer) result).intValue()
							: ((Long) result).intValue();
		}catch(Exception e){
			LOG.error("count student failed!", e);
		}
		return 0;
	}

	@Override
	public List<Score> queryCourseNoPassByStudent(Long studentId, Limit limit) {
		// TODO Auto-generated method stub
		
		StringBuffer hql = new StringBuffer();
		hql.append("select c from Score c left join fetch c.course left join fetch c.course.teacher where c.point >0 and c.point <60 ");
		if(studentId !=0){
			hql.append(" and c.student.id=:studentId");
		}
		
		
		hql.append(" order by c.createDate desc");
		List<Score> scores = null;
		try{
			Query query = this.query(hql.toString());
			if(studentId !=0){
				query.setParameter("studentId", studentId);
			}
			
			
			
			if (limit != null) {
				query.setFirstResult((limit.getPage() - 1)
						* limit.getCurrentRowsDisplayed());
				query.setMaxResults(limit.getCurrentRowsDisplayed());
			}
			scores =  query.getResultList();
		}catch(Exception e){
			LOG.error("query student failed!", e);
		}
		return scores;
	}
}
